Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
86.56% |
380 / 439 |
|
57.14% |
28 / 49 |
CRAP | |
0.00% |
0 / 1 |
| SelectQueryBuilder | |
86.56% |
380 / 439 |
|
57.14% |
28 / 49 |
240.16 | |
0.00% |
0 / 1 |
| __construct | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
1 | |||
| setTable | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| setPrefix | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| setCteManager | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| setUnions | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| setDistinct | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| setDistinctOn | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
| select | |
96.00% |
24 / 25 |
|
0.00% |
0 / 1 |
12 | |||
| get | |
100.00% |
14 / 14 |
|
100.00% |
1 / 1 |
3 | |||
| getOne | |
78.57% |
11 / 14 |
|
0.00% |
0 / 1 |
3.09 | |||
| getColumn | |
84.62% |
11 / 13 |
|
0.00% |
0 / 1 |
5.09 | |||
| getValue | |
80.95% |
17 / 21 |
|
0.00% |
0 / 1 |
9.56 | |||
| orderBy | |
90.62% |
29 / 32 |
|
0.00% |
0 / 1 |
14.16 | |||
| addOrderExpression | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| cache | |
62.50% |
5 / 8 |
|
0.00% |
0 / 1 |
2.21 | |||
| noCache | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| groupBy | |
91.67% |
11 / 12 |
|
0.00% |
0 / 1 |
5.01 | |||
| limit | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| offset | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| option | |
85.71% |
6 / 7 |
|
0.00% |
0 / 1 |
4.05 | |||
| asObject | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| toSQL | |
100.00% |
13 / 13 |
|
100.00% |
1 / 1 |
4 | |||
| explain | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| explainAnalyze | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| explainAdvice | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| describe | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| indexes | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| keys | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| constraints | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| getQuery | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| buildSelectSql | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
3 | |||
| getQueryStructure | |
100.00% |
19 / 19 |
|
100.00% |
1 / 1 |
3 | |||
| compileSelectSql | |
79.63% |
43 / 54 |
|
0.00% |
0 / 1 |
23.38 | |||
| setCompilationCache | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
| buildUnionSql | |
70.59% |
12 / 17 |
|
0.00% |
0 / 1 |
5.64 | |||
| resolveSelectedKey | |
88.89% |
8 / 9 |
|
0.00% |
0 / 1 |
4.02 | |||
| isTableInCurrentQuery | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
4 | |||
| getCurrentTables | |
85.71% |
6 / 7 |
|
0.00% |
0 / 1 |
4.05 | |||
| extractTableAlias | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| paginate | |
84.62% |
22 / 26 |
|
0.00% |
0 / 1 |
7.18 | |||
| simplePaginate | |
88.89% |
8 / 9 |
|
0.00% |
0 / 1 |
4.02 | |||
| cursorPaginate | |
83.33% |
15 / 18 |
|
0.00% |
0 / 1 |
9.37 | |||
| getCursorColumns | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
3 | |||
| applyCursorConditions | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
3 | |||
| shouldUseCache | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
2 | |||
| getFromCache | |
83.33% |
10 / 12 |
|
0.00% |
0 / 1 |
6.17 | |||
| saveToCache | |
77.78% |
7 / 9 |
|
0.00% |
0 / 1 |
4.18 | |||
| generateCacheKey | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
12 | |||
| generateCacheKeyFromSqlData | |
75.00% |
3 / 4 |
|
0.00% |
0 / 1 |
2.06 | |||
| 1 | <?php |
| 2 | |
| 3 | declare(strict_types=1); |
| 4 | |
| 5 | namespace tommyknocker\pdodb\query; |
| 6 | |
| 7 | use PDO; |
| 8 | use PDOException; |
| 9 | use RuntimeException; |
| 10 | use tommyknocker\pdodb\cache\CacheManager; |
| 11 | use tommyknocker\pdodb\connection\ConnectionInterface; |
| 12 | use tommyknocker\pdodb\helpers\values\RawValue; |
| 13 | use tommyknocker\pdodb\query\analysis\ExplainAnalyzer; |
| 14 | use tommyknocker\pdodb\query\cache\QueryCompilationCache; |
| 15 | use tommyknocker\pdodb\query\cte\CteManager; |
| 16 | use tommyknocker\pdodb\query\interfaces\ConditionBuilderInterface; |
| 17 | use tommyknocker\pdodb\query\interfaces\ExecutionEngineInterface; |
| 18 | use tommyknocker\pdodb\query\interfaces\JoinBuilderInterface; |
| 19 | use tommyknocker\pdodb\query\interfaces\ParameterManagerInterface; |
| 20 | use tommyknocker\pdodb\query\interfaces\SelectQueryBuilderInterface; |
| 21 | use tommyknocker\pdodb\query\pagination\Cursor; |
| 22 | use tommyknocker\pdodb\query\pagination\CursorPaginationResult; |
| 23 | use tommyknocker\pdodb\query\pagination\PaginationResult; |
| 24 | use tommyknocker\pdodb\query\pagination\SimplePaginationResult; |
| 25 | use tommyknocker\pdodb\query\traits\CommonDependenciesTrait; |
| 26 | use tommyknocker\pdodb\query\traits\ExternalReferenceProcessingTrait; |
| 27 | use tommyknocker\pdodb\query\traits\IdentifierQuotingTrait; |
| 28 | use tommyknocker\pdodb\query\traits\RawValueResolutionTrait; |
| 29 | use tommyknocker\pdodb\query\traits\TableManagementTrait; |
| 30 | |
| 31 | class SelectQueryBuilder implements SelectQueryBuilderInterface |
| 32 | { |
| 33 | use CommonDependenciesTrait; |
| 34 | use RawValueResolutionTrait; |
| 35 | use TableManagementTrait; |
| 36 | use IdentifierQuotingTrait; |
| 37 | use ExternalReferenceProcessingTrait; |
| 38 | |
| 39 | /** @var string|null table name */ |
| 40 | protected ?string $table = null { |
| 41 | get { |
| 42 | if (!$this->table) { |
| 43 | throw new RuntimeException('You must define table first. Use table() or from() methods'); |
| 44 | } |
| 45 | return $this->table; |
| 46 | } |
| 47 | } |
| 48 | |
| 49 | /** @var array<int, string> */ |
| 50 | protected array $select = []; |
| 51 | |
| 52 | /** @var array<int, string> ORDER BY expressions */ |
| 53 | protected array $order = []; |
| 54 | |
| 55 | /** @var string|null GROUP BY expression */ |
| 56 | protected ?string $group = null; |
| 57 | |
| 58 | /** @var int|null LIMIT value */ |
| 59 | protected ?int $limit = null; |
| 60 | |
| 61 | /** @var int|null OFFSET value */ |
| 62 | protected ?int $offset = null; |
| 63 | |
| 64 | /** @var int PDO fetch mode */ |
| 65 | protected int $fetchMode = PDO::FETCH_ASSOC; |
| 66 | |
| 67 | /** @var array<int|string, mixed> Query options (e.g., FOR UPDATE, IGNORE) */ |
| 68 | protected array $options = []; |
| 69 | |
| 70 | /** @var CacheManager|null Cache manager instance */ |
| 71 | protected ?CacheManager $cacheManager = null; |
| 72 | |
| 73 | /** @var QueryCompilationCache|null Query compilation cache instance */ |
| 74 | protected ?QueryCompilationCache $compilationCache = null; |
| 75 | |
| 76 | /** @var bool Whether caching is enabled for this query */ |
| 77 | protected bool $cacheEnabled = false; |
| 78 | |
| 79 | /** @var int|null Cache TTL in seconds */ |
| 80 | protected ?int $cacheTtl = null; |
| 81 | |
| 82 | /** @var string|null Custom cache key */ |
| 83 | protected ?string $cacheKey = null; |
| 84 | |
| 85 | /** @var CteManager|null CTE manager for WITH clauses */ |
| 86 | protected ?CteManager $cteManager = null; |
| 87 | |
| 88 | /** @var array<UnionQuery> Array of UNION/INTERSECT/EXCEPT operations */ |
| 89 | protected array $unions = []; |
| 90 | |
| 91 | /** @var bool Whether to use DISTINCT */ |
| 92 | protected bool $distinct = false; |
| 93 | |
| 94 | /** @var array<string> Columns for DISTINCT ON (PostgreSQL) */ |
| 95 | protected array $distinctOn = []; |
| 96 | |
| 97 | /** @var array{sql: string, params: array<string, mixed>}|null Cached SQL data to avoid double compilation */ |
| 98 | protected ?array $cachedSqlData = null; |
| 99 | |
| 100 | /** @var string|null Cached cache key to avoid regenerating it */ |
| 101 | protected ?string $cachedCacheKey = null; |
| 102 | |
| 103 | protected ConditionBuilderInterface $conditionBuilder; |
| 104 | protected JoinBuilderInterface $joinBuilder; |
| 105 | |
| 106 | public function __construct( |
| 107 | ConnectionInterface $connection, |
| 108 | ParameterManagerInterface $parameterManager, |
| 109 | ExecutionEngineInterface $executionEngine, |
| 110 | ConditionBuilderInterface $conditionBuilder, |
| 111 | JoinBuilderInterface $joinBuilder, |
| 112 | RawValueResolver $rawValueResolver, |
| 113 | ?CacheManager $cacheManager = null, |
| 114 | ?QueryCompilationCache $compilationCache = null |
| 115 | ) { |
| 116 | $this->initializeCommonDependencies($connection, $parameterManager, $executionEngine, $rawValueResolver); |
| 117 | $this->conditionBuilder = $conditionBuilder; |
| 118 | $this->joinBuilder = $joinBuilder; |
| 119 | $this->cacheManager = $cacheManager; |
| 120 | $this->compilationCache = $compilationCache; |
| 121 | } |
| 122 | |
| 123 | /** |
| 124 | * Set table name. |
| 125 | * |
| 126 | * @param string $table |
| 127 | * |
| 128 | * @return static |
| 129 | */ |
| 130 | public function setTable(string $table): static |
| 131 | { |
| 132 | $this->table = $table; |
| 133 | $this->conditionBuilder->setTable($table); |
| 134 | return $this; |
| 135 | } |
| 136 | |
| 137 | /** |
| 138 | * Set table prefix. |
| 139 | * |
| 140 | * @param string|null $prefix |
| 141 | * |
| 142 | * @return static |
| 143 | */ |
| 144 | public function setPrefix(?string $prefix): static |
| 145 | { |
| 146 | $this->prefix = $prefix; |
| 147 | $this->conditionBuilder->setPrefix($prefix); |
| 148 | $this->joinBuilder->setPrefix($prefix); |
| 149 | return $this; |
| 150 | } |
| 151 | |
| 152 | /** |
| 153 | * Set CTE manager. |
| 154 | * |
| 155 | * @param CteManager|null $cteManager |
| 156 | * |
| 157 | * @return static |
| 158 | */ |
| 159 | public function setCteManager(?CteManager $cteManager): static |
| 160 | { |
| 161 | $this->cteManager = $cteManager; |
| 162 | return $this; |
| 163 | } |
| 164 | |
| 165 | /** |
| 166 | * Set UNION operations. |
| 167 | * |
| 168 | * @param array<UnionQuery> $unions Array of union operations. |
| 169 | * |
| 170 | * @return static |
| 171 | */ |
| 172 | public function setUnions(array $unions): static |
| 173 | { |
| 174 | $this->unions = $unions; |
| 175 | return $this; |
| 176 | } |
| 177 | |
| 178 | /** |
| 179 | * Set DISTINCT flag. |
| 180 | * |
| 181 | * @param bool $distinct Whether to use DISTINCT. |
| 182 | * |
| 183 | * @return static |
| 184 | */ |
| 185 | public function setDistinct(bool $distinct): static |
| 186 | { |
| 187 | $this->distinct = $distinct; |
| 188 | return $this; |
| 189 | } |
| 190 | |
| 191 | /** |
| 192 | * Set DISTINCT ON columns. |
| 193 | * |
| 194 | * @param array<string> $columns Columns for DISTINCT ON. |
| 195 | * |
| 196 | * @return static |
| 197 | */ |
| 198 | public function setDistinctOn(array $columns): static |
| 199 | { |
| 200 | $this->distinctOn = $columns; |
| 201 | return $this; |
| 202 | } |
| 203 | |
| 204 | /** |
| 205 | * Add columns to the SELECT clause. |
| 206 | * |
| 207 | * @param RawValue|callable(QueryBuilder): void|string|array<int|string, string|RawValue|callable(QueryBuilder): void> $cols The columns to add. |
| 208 | * |
| 209 | * @return static The current instance. |
| 210 | */ |
| 211 | public function select(RawValue|callable|string|array $cols): static |
| 212 | { |
| 213 | if (!is_array($cols)) { |
| 214 | $cols = [$cols]; |
| 215 | } |
| 216 | foreach ($cols as $index => $col) { |
| 217 | if ($col instanceof RawValue && is_string($index)) { |
| 218 | $this->select[] = $this->resolveRawValue($col) . ' AS ' . $index; |
| 219 | } elseif ($col instanceof RawValue) { |
| 220 | $this->select[] = $this->resolveRawValue($col); |
| 221 | } elseif (is_callable($col)) { |
| 222 | // Handle callback for subqueries |
| 223 | $subQuery = new QueryBuilder($this->connection, $this->prefix ?? ''); |
| 224 | $col($subQuery); |
| 225 | $sub = $subQuery->toSQL(); |
| 226 | $map = $this->parameterManager->mergeSubParams($sub['params'], 'sq'); |
| 227 | $subSql = $this->parameterManager->replacePlaceholdersInSql($sub['sql'], $map); |
| 228 | $this->select[] = is_string($index) ? "({$subSql}) AS {$index}" : "({$subSql})"; |
| 229 | } elseif (is_string($index)) { // ['total' => 'SUM(amount)] Treat it as SUM(amount) AS total |
| 230 | // Process external references in column expressions |
| 231 | $processedCol = $this->processExternalReferences($col); |
| 232 | if ($processedCol instanceof RawValue) { |
| 233 | $this->select[] = $this->resolveRawValue($processedCol) . ' AS ' . $index; |
| 234 | } else { |
| 235 | $colStr = is_string($col) ? $col : (string)$col; |
| 236 | $this->select[] = $colStr . ' AS ' . $index; |
| 237 | } |
| 238 | } else { |
| 239 | // Process external references in column names |
| 240 | $processedCol = $this->processExternalReferences($col); |
| 241 | if ($processedCol instanceof RawValue) { |
| 242 | $this->select[] = $this->resolveRawValue($processedCol); |
| 243 | } else { |
| 244 | $this->select[] = $col; |
| 245 | } |
| 246 | } |
| 247 | } |
| 248 | return $this; |
| 249 | } |
| 250 | |
| 251 | /** |
| 252 | * Execute SELECT statement and return all rows. |
| 253 | * |
| 254 | * @return array<int, array<string, mixed>> |
| 255 | * @throws PDOException |
| 256 | */ |
| 257 | public function get(): array |
| 258 | { |
| 259 | // Fast path: if cache is disabled, skip all cache operations |
| 260 | if (!$this->shouldUseCache()) { |
| 261 | $sqlData = $this->toSQL(); |
| 262 | return $this->executionEngine->fetchAll($sqlData['sql'], $sqlData['params']); |
| 263 | } |
| 264 | |
| 265 | // Cache enabled: try to get from cache first |
| 266 | $cached = $this->getFromCache(); |
| 267 | if (is_array($cached)) { |
| 268 | // Cache hit: return immediately, no SQL compilation needed |
| 269 | $this->cachedSqlData = null; |
| 270 | $this->cachedCacheKey = null; |
| 271 | return $cached; |
| 272 | } |
| 273 | |
| 274 | // Cache miss: use cached SQL data if available (from getFromCache call) |
| 275 | $sqlData = $this->cachedSqlData ?? $this->toSQL(); |
| 276 | |
| 277 | $result = $this->executionEngine->fetchAll($sqlData['sql'], $sqlData['params']); |
| 278 | |
| 279 | // Save to cache (uses cached key if available) |
| 280 | $this->saveToCache($result); |
| 281 | |
| 282 | // Clear cache after use |
| 283 | $this->cachedSqlData = null; |
| 284 | $this->cachedCacheKey = null; |
| 285 | |
| 286 | return $result; |
| 287 | } |
| 288 | |
| 289 | /** |
| 290 | * Execute SELECT statement and return first row. |
| 291 | * |
| 292 | * @return mixed |
| 293 | * @throws PDOException |
| 294 | */ |
| 295 | public function getOne(): mixed |
| 296 | { |
| 297 | // Fast path: if cache is disabled, skip all cache operations |
| 298 | if (!$this->shouldUseCache()) { |
| 299 | $sqlData = $this->toSQL(); |
| 300 | return $this->executionEngine->fetch($sqlData['sql'], $sqlData['params']); |
| 301 | } |
| 302 | |
| 303 | // Cache enabled: try to get from cache first |
| 304 | $cached = $this->getFromCache(); |
| 305 | if ($cached !== null) { |
| 306 | // Cache hit: return immediately, no SQL compilation needed |
| 307 | $this->cachedSqlData = null; |
| 308 | $this->cachedCacheKey = null; |
| 309 | return $cached; |
| 310 | } |
| 311 | |
| 312 | // Cache miss: use cached SQL data if available (from getFromCache call) |
| 313 | $sqlData = $this->cachedSqlData ?? $this->toSQL(); |
| 314 | |
| 315 | $result = $this->executionEngine->fetch($sqlData['sql'], $sqlData['params']); |
| 316 | |
| 317 | // Save to cache (uses cached key if available) |
| 318 | $this->saveToCache($result); |
| 319 | |
| 320 | // Clear cache after use |
| 321 | $this->cachedSqlData = null; |
| 322 | $this->cachedCacheKey = null; |
| 323 | |
| 324 | return $result; |
| 325 | } |
| 326 | |
| 327 | /** |
| 328 | * Execute SELECT statement and return column values. |
| 329 | * |
| 330 | * @return array<int, mixed> |
| 331 | * @throws PDOException |
| 332 | */ |
| 333 | public function getColumn(): array |
| 334 | { |
| 335 | if (count($this->select) !== 1) { |
| 336 | return []; |
| 337 | } |
| 338 | |
| 339 | if ($this->shouldUseCache()) { |
| 340 | $cached = $this->getFromCache(); |
| 341 | if ($cached !== null) { |
| 342 | // Clear cached SQL data since we didn't execute |
| 343 | $this->cachedSqlData = null; |
| 344 | return $cached; |
| 345 | } |
| 346 | } |
| 347 | |
| 348 | $key = $this->resolveSelectedKey(); |
| 349 | $rows = $this->get(); |
| 350 | $result = array_column($rows, $key); |
| 351 | |
| 352 | if ($this->shouldUseCache()) { |
| 353 | $this->saveToCache($result); |
| 354 | } |
| 355 | |
| 356 | return $result; |
| 357 | } |
| 358 | |
| 359 | /** |
| 360 | * Execute SELECT statement and return single value. |
| 361 | * |
| 362 | * @return mixed |
| 363 | * @throws PDOException |
| 364 | */ |
| 365 | public function getValue(): mixed |
| 366 | { |
| 367 | if (count($this->select) !== 1) { |
| 368 | return false; |
| 369 | } |
| 370 | |
| 371 | if ($this->shouldUseCache()) { |
| 372 | $cached = $this->getFromCache(); |
| 373 | if ($cached !== null) { |
| 374 | // Clear cached SQL data since we didn't execute |
| 375 | $this->cachedSqlData = null; |
| 376 | $this->cachedCacheKey = null; |
| 377 | return $cached; |
| 378 | } |
| 379 | } |
| 380 | |
| 381 | // Temporarily disable cache for getOne() call to avoid double caching |
| 382 | // We'll cache the final value ourselves |
| 383 | $wasCacheEnabled = $this->cacheEnabled; |
| 384 | $this->cacheEnabled = false; |
| 385 | |
| 386 | $row = $this->getOne(); |
| 387 | |
| 388 | // Restore cache setting |
| 389 | $this->cacheEnabled = $wasCacheEnabled; |
| 390 | |
| 391 | // Check if row is valid array |
| 392 | if (!is_array($row) || empty($row)) { |
| 393 | return null; |
| 394 | } |
| 395 | |
| 396 | $key = $this->resolveSelectedKey(); |
| 397 | if (count($row) === 1 && !isset($row[$key])) { |
| 398 | $result = array_shift($row); |
| 399 | } else { |
| 400 | $result = $row[$key] ?? null; |
| 401 | } |
| 402 | |
| 403 | if ($this->shouldUseCache()) { |
| 404 | $this->saveToCache($result); |
| 405 | } |
| 406 | |
| 407 | return $result; |
| 408 | } |
| 409 | |
| 410 | /** |
| 411 | * Add ORDER BY clause. |
| 412 | * |
| 413 | * @param string|array<int|string, string>|RawValue $expr The expression(s) to order by. |
| 414 | * - string: 'column' or 'column ASC' or 'column1 ASC, column2 DESC' |
| 415 | * - array: ['column1', 'column2'] or ['column1' => 'ASC', 'column2' => 'DESC'] |
| 416 | * - RawValue: raw SQL expression |
| 417 | * @param string $direction The direction of the ordering (ASC or DESC). Ignored when expr is array. |
| 418 | * |
| 419 | * @return static The current instance. |
| 420 | */ |
| 421 | public function orderBy(string|array|RawValue $expr, string $direction = 'ASC'): static |
| 422 | { |
| 423 | // Handle array of columns |
| 424 | if (is_array($expr)) { |
| 425 | foreach ($expr as $col => $dir) { |
| 426 | if (is_int($col)) { |
| 427 | // Numeric key: ['column1', 'column2'] - use default direction |
| 428 | $this->orderBy($dir, $direction); |
| 429 | } else { |
| 430 | // Associative: ['column1' => 'ASC', 'column2' => 'DESC'] |
| 431 | $this->orderBy($col, $dir); |
| 432 | } |
| 433 | } |
| 434 | return $this; |
| 435 | } |
| 436 | |
| 437 | $dir = strtoupper(trim($direction)); |
| 438 | if ($dir !== 'ASC' && $dir !== 'DESC') { |
| 439 | $dir = 'ASC'; |
| 440 | } |
| 441 | |
| 442 | if ($expr instanceof RawValue) { |
| 443 | $this->order[] = $this->resolveRawValue($expr) . ' ' . $dir; |
| 444 | } elseif (str_contains($expr, ',')) { |
| 445 | // Handle comma-separated: 'column1 ASC, column2 DESC' |
| 446 | $parts = array_map('trim', explode(',', $expr)); |
| 447 | foreach ($parts as $part) { |
| 448 | if (preg_match('/^(.+?)\s+(ASC|DESC)$/i', $part, $matches)) { |
| 449 | $col = trim($matches[1]); |
| 450 | $partDir = strtoupper($matches[2]); |
| 451 | $processedExpr = $this->processExternalReferences($col); |
| 452 | if ($processedExpr instanceof RawValue) { |
| 453 | $this->order[] = $this->resolveRawValue($processedExpr) . ' ' . $partDir; |
| 454 | } else { |
| 455 | $this->order[] = $this->quoteQualifiedIdentifier($col) . ' ' . $partDir; |
| 456 | } |
| 457 | } else { |
| 458 | // No direction specified, use default |
| 459 | $processedExpr = $this->processExternalReferences($part); |
| 460 | if ($processedExpr instanceof RawValue) { |
| 461 | $this->order[] = $this->resolveRawValue($processedExpr) . ' ' . $dir; |
| 462 | } else { |
| 463 | $this->order[] = $this->quoteQualifiedIdentifier($part) . ' ' . $dir; |
| 464 | } |
| 465 | } |
| 466 | } |
| 467 | } elseif (preg_match('/^[a-z0-9._`"]+\s+(ASC|DESC)$/iu', $expr)) { |
| 468 | // Single column with direction: 'column ASC' |
| 469 | $this->order[] = $expr; |
| 470 | } else { |
| 471 | // Process external references |
| 472 | $processedExpr = $this->processExternalReferences($expr); |
| 473 | if ($processedExpr instanceof RawValue) { |
| 474 | $this->order[] = $this->resolveRawValue($processedExpr) . ' ' . $dir; |
| 475 | } else { |
| 476 | $this->order[] = $this->quoteQualifiedIdentifier($expr) . ' ' . $dir; |
| 477 | } |
| 478 | } |
| 479 | |
| 480 | return $this; |
| 481 | } |
| 482 | |
| 483 | /** |
| 484 | * Add ORDER BY expression directly (for JSON expressions that already contain direction). |
| 485 | * |
| 486 | * @param string $expr The complete ORDER BY expression. |
| 487 | * |
| 488 | * @return static The current instance. |
| 489 | */ |
| 490 | public function addOrderExpression(string $expr): static |
| 491 | { |
| 492 | $this->order[] = $expr; |
| 493 | return $this; |
| 494 | } |
| 495 | |
| 496 | /** |
| 497 | * Enable caching for this query. |
| 498 | * |
| 499 | * @param int $ttl Time-to-live in seconds (0 = disable cache for this query) |
| 500 | * @param string|null $key Custom cache key (null = auto-generate) |
| 501 | * |
| 502 | * @return static The current instance. |
| 503 | */ |
| 504 | public function cache(int $ttl = 3600, ?string $key = null): static |
| 505 | { |
| 506 | if ($ttl <= 0) { |
| 507 | // TTL of 0 or negative means disable cache for this query |
| 508 | $this->cacheEnabled = false; |
| 509 | $this->cacheTtl = null; |
| 510 | $this->cacheKey = null; |
| 511 | } else { |
| 512 | $this->cacheEnabled = true; |
| 513 | $this->cacheTtl = $ttl; |
| 514 | $this->cacheKey = $key; |
| 515 | } |
| 516 | return $this; |
| 517 | } |
| 518 | |
| 519 | /** |
| 520 | * Disable caching for this query. |
| 521 | * |
| 522 | * @return static The current instance. |
| 523 | */ |
| 524 | public function noCache(): static |
| 525 | { |
| 526 | $this->cacheEnabled = false; |
| 527 | $this->cacheTtl = null; |
| 528 | $this->cacheKey = null; |
| 529 | return $this; |
| 530 | } |
| 531 | |
| 532 | /** |
| 533 | * Add GROUP BY clause. |
| 534 | * |
| 535 | * @param string|array<int, string|RawValue>|RawValue $cols The columns to group by. |
| 536 | * |
| 537 | * @return static The current instance. |
| 538 | */ |
| 539 | public function groupBy(string|array|RawValue $cols): static |
| 540 | { |
| 541 | if (!is_array($cols)) { |
| 542 | $cols = [$cols]; |
| 543 | } |
| 544 | $groups = []; |
| 545 | foreach ($cols as $col) { |
| 546 | if ($col instanceof RawValue) { |
| 547 | $groups[] = $this->resolveRawValue($col); |
| 548 | } else { |
| 549 | // Process external references |
| 550 | $processedCol = $this->processExternalReferences($col); |
| 551 | if ($processedCol instanceof RawValue) { |
| 552 | $groups[] = $this->resolveRawValue($processedCol); |
| 553 | } else { |
| 554 | $groups[] = $this->quoteQualifiedIdentifier((string)$col); |
| 555 | } |
| 556 | } |
| 557 | } |
| 558 | $this->group = implode(', ', $groups); |
| 559 | return $this; |
| 560 | } |
| 561 | |
| 562 | /** |
| 563 | * Add LIMIT clause. |
| 564 | * |
| 565 | * @param int $number The number of rows to limit. |
| 566 | * |
| 567 | * @return static The current instance. |
| 568 | */ |
| 569 | public function limit(int $number): static |
| 570 | { |
| 571 | $this->limit = $number; |
| 572 | $this->conditionBuilder->setLimit($number); |
| 573 | return $this; |
| 574 | } |
| 575 | |
| 576 | /** |
| 577 | * Add OFFSET clause. |
| 578 | * |
| 579 | * @param int $number The number of rows to offset. |
| 580 | * |
| 581 | * @return static The current instance. |
| 582 | */ |
| 583 | public function offset(int $number): static |
| 584 | { |
| 585 | $this->offset = $number; |
| 586 | return $this; |
| 587 | } |
| 588 | |
| 589 | /** |
| 590 | * Sets the query options. |
| 591 | * |
| 592 | * @param string|array<int|string, mixed> $options The query options. |
| 593 | * |
| 594 | * @return static The current object. |
| 595 | */ |
| 596 | public function option(string|array $options): static |
| 597 | { |
| 598 | if (is_array($options)) { |
| 599 | foreach ($options as $key => $value) { |
| 600 | if (is_string($key)) { |
| 601 | $this->options[$key] = $value; |
| 602 | } else { |
| 603 | $this->options[] = $value; |
| 604 | } |
| 605 | } |
| 606 | } else { |
| 607 | $this->options[] = $options; |
| 608 | } |
| 609 | return $this; |
| 610 | } |
| 611 | |
| 612 | /** |
| 613 | * Set fetch mode to return objects. |
| 614 | * |
| 615 | * @return static |
| 616 | */ |
| 617 | public function asObject(): static |
| 618 | { |
| 619 | $this->fetchMode = PDO::FETCH_OBJ; |
| 620 | $this->executionEngine->setFetchMode(PDO::FETCH_OBJ); |
| 621 | return $this; |
| 622 | } |
| 623 | |
| 624 | /** |
| 625 | * Convert query to SQL string and parameters. |
| 626 | * |
| 627 | * @param bool $formatted Whether to format SQL for readability |
| 628 | * |
| 629 | * @return array{sql: string, params: array<string, string|int|float|bool|null>} |
| 630 | */ |
| 631 | public function toSQL(bool $formatted = false): array |
| 632 | { |
| 633 | $sql = $this->buildSelectSql(); |
| 634 | $params = $this->parameterManager->getParams(); |
| 635 | |
| 636 | // Merge CTE parameters if CTE manager exists |
| 637 | if ($this->cteManager && !$this->cteManager->isEmpty()) { |
| 638 | $cteParams = $this->cteManager->getParams(); |
| 639 | $params = array_merge($cteParams, $params); |
| 640 | } |
| 641 | |
| 642 | // Format SQL if requested |
| 643 | if ($formatted) { |
| 644 | $formatter = new \tommyknocker\pdodb\query\formatter\SqlFormatter( |
| 645 | highlightKeywords: false, |
| 646 | indentSize: 4, |
| 647 | indentChar: ' ' |
| 648 | ); |
| 649 | $sql = $formatter->format($sql); |
| 650 | } |
| 651 | |
| 652 | return ['sql' => $sql, 'params' => $params]; |
| 653 | } |
| 654 | |
| 655 | /** |
| 656 | * Execute EXPLAIN query to analyze query execution plan. |
| 657 | * |
| 658 | * @return array<int, array<string, mixed>> |
| 659 | * @throws PDOException |
| 660 | */ |
| 661 | public function explain(): array |
| 662 | { |
| 663 | $sqlData = $this->toSQL(); |
| 664 | $explainSql = $this->dialect->buildExplainSql($sqlData['sql']); |
| 665 | return $this->executionEngine->fetchAll($explainSql, $sqlData['params']); |
| 666 | } |
| 667 | |
| 668 | /** |
| 669 | * Execute EXPLAIN ANALYZE query (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL). |
| 670 | * |
| 671 | * @return array<int, array<string, mixed>> |
| 672 | * @throws PDOException |
| 673 | */ |
| 674 | public function explainAnalyze(): array |
| 675 | { |
| 676 | $sqlData = $this->toSQL(); |
| 677 | $explainSql = $this->dialect->buildExplainAnalyzeSql($sqlData['sql']); |
| 678 | return $this->executionEngine->fetchAll($explainSql, $sqlData['params']); |
| 679 | } |
| 680 | |
| 681 | /** |
| 682 | * Analyze EXPLAIN output with optimization recommendations. |
| 683 | * |
| 684 | * @param string|null $tableName Optional table name for index suggestions |
| 685 | * |
| 686 | * @return \tommyknocker\pdodb\query\analysis\ExplainAnalysis Analysis result with recommendations |
| 687 | */ |
| 688 | public function explainAdvice(?string $tableName = null): \tommyknocker\pdodb\query\analysis\ExplainAnalysis |
| 689 | { |
| 690 | $sqlData = $this->toSQL(); |
| 691 | $explainSql = $this->dialect->buildExplainSql($sqlData['sql']); |
| 692 | $explainResults = $this->executionEngine->fetchAll($explainSql, $sqlData['params']); |
| 693 | |
| 694 | $analyzer = new ExplainAnalyzer($this->dialect, $this->executionEngine); |
| 695 | $targetTable = $tableName ?? $this->table; |
| 696 | |
| 697 | return $analyzer->analyze($explainResults, $targetTable); |
| 698 | } |
| 699 | |
| 700 | /** |
| 701 | * Execute DESCRIBE query to get table structure. |
| 702 | * |
| 703 | * @return array<int, array<string, mixed>> |
| 704 | * @throws PDOException |
| 705 | */ |
| 706 | public function describe(): array |
| 707 | { |
| 708 | $tableName = $this->table; // Use getter to ensure not null |
| 709 | assert(is_string($tableName)); // PHPStan assertion |
| 710 | $describeSql = $this->dialect->buildDescribeSql($tableName); |
| 711 | return $this->executionEngine->fetchAll($describeSql); |
| 712 | } |
| 713 | |
| 714 | /** |
| 715 | * Get indexes for the current table. |
| 716 | * |
| 717 | * @return array<int, array<string, mixed>> |
| 718 | */ |
| 719 | public function indexes(): array |
| 720 | { |
| 721 | $tableName = $this->table; |
| 722 | assert(is_string($tableName)); |
| 723 | $sql = $this->dialect->buildShowIndexesSql($tableName); |
| 724 | return $this->executionEngine->fetchAll($sql); |
| 725 | } |
| 726 | |
| 727 | /** |
| 728 | * Get foreign keys for the current table. |
| 729 | * |
| 730 | * @return array<int, array<string, mixed>> |
| 731 | */ |
| 732 | public function keys(): array |
| 733 | { |
| 734 | $tableName = $this->table; |
| 735 | assert(is_string($tableName)); |
| 736 | $sql = $this->dialect->buildShowForeignKeysSql($tableName); |
| 737 | return $this->executionEngine->fetchAll($sql); |
| 738 | } |
| 739 | |
| 740 | /** |
| 741 | * Get constraints for the current table. |
| 742 | * |
| 743 | * @return array<int, array<string, mixed>> |
| 744 | */ |
| 745 | public function constraints(): array |
| 746 | { |
| 747 | $tableName = $this->table; |
| 748 | assert(is_string($tableName)); |
| 749 | $sql = $this->dialect->buildShowConstraintsSql($tableName); |
| 750 | return $this->executionEngine->fetchAll($sql); |
| 751 | } |
| 752 | |
| 753 | /** |
| 754 | * Get the current query SQL and parameters. |
| 755 | * |
| 756 | * @return array{sql: string, params: array<string, mixed>} |
| 757 | */ |
| 758 | public function getQuery(): array |
| 759 | { |
| 760 | return $this->toSQL(); |
| 761 | } |
| 762 | |
| 763 | /** |
| 764 | * Build SELECT sql. |
| 765 | * |
| 766 | * @return string |
| 767 | */ |
| 768 | public function buildSelectSql(): string |
| 769 | { |
| 770 | // Try to get from compilation cache if enabled |
| 771 | if ($this->compilationCache !== null && $this->compilationCache->isEnabled()) { |
| 772 | $structure = $this->getQueryStructure(); |
| 773 | $driver = $this->connection->getDriverName(); |
| 774 | |
| 775 | return $this->compilationCache->getOrCompile( |
| 776 | fn (): string => $this->compileSelectSql(), |
| 777 | $structure, |
| 778 | $driver |
| 779 | ); |
| 780 | } |
| 781 | |
| 782 | return $this->compileSelectSql(); |
| 783 | } |
| 784 | |
| 785 | /** |
| 786 | * Get query structure for caching purposes. |
| 787 | * |
| 788 | * @return array<string, mixed> |
| 789 | */ |
| 790 | protected function getQueryStructure(): array |
| 791 | { |
| 792 | $where = $this->conditionBuilder->getWhere(); |
| 793 | $having = $this->conditionBuilder->getHaving(); |
| 794 | |
| 795 | // Extract CTE info |
| 796 | $hasCte = $this->cteManager !== null && !$this->cteManager->isEmpty(); |
| 797 | |
| 798 | return [ |
| 799 | 'table' => $this->table, |
| 800 | 'select' => $this->select, |
| 801 | 'distinct' => $this->distinct, |
| 802 | 'distinct_on' => $this->distinctOn, |
| 803 | 'joins' => $this->joinBuilder->getJoins(), |
| 804 | 'where' => $where, |
| 805 | 'group_by' => $this->group, |
| 806 | 'having' => $having, |
| 807 | 'order_by' => $this->order, |
| 808 | 'limit' => $this->limit, |
| 809 | 'offset' => $this->offset, |
| 810 | 'options' => $this->options, |
| 811 | 'unions' => $this->unions, |
| 812 | 'cte' => $hasCte ? true : null, |
| 813 | ]; |
| 814 | } |
| 815 | |
| 816 | /** |
| 817 | * Compile SELECT SQL (internal method, called directly or via cache). |
| 818 | * |
| 819 | * @return string |
| 820 | */ |
| 821 | protected function compileSelectSql(): string |
| 822 | { |
| 823 | $sql = ''; |
| 824 | |
| 825 | // Add WITH clause if CTEs exist |
| 826 | if ($this->cteManager && !$this->cteManager->isEmpty()) { |
| 827 | $sql = $this->cteManager->buildSql() . ' '; |
| 828 | } |
| 829 | |
| 830 | // build base select (no DB-specific option handling) |
| 831 | if (empty($this->select)) { |
| 832 | $select = '*'; |
| 833 | } else { |
| 834 | $select = implode(', ', array_map(function ($value) { |
| 835 | // Check if it's already a compiled subquery (starts with '(') |
| 836 | if (str_starts_with($value, '(')) { |
| 837 | return $value; |
| 838 | } |
| 839 | // Allow wildcards and preformatted lists/expressions: |
| 840 | // - "*" |
| 841 | // - "alias.*" |
| 842 | // - lists like "a.*, b.*" |
| 843 | // - any expression containing commas or '*' should pass through |
| 844 | if ($value === '*') { |
| 845 | return $value; |
| 846 | } |
| 847 | // alias.* |
| 848 | if (preg_match('/^[A-Za-z_][A-Za-z0-9_]*\\.\*$/', $value) === 1) { |
| 849 | return $value; |
| 850 | } |
| 851 | // multiple alias.* segments separated by comma: a.*, b.* |
| 852 | if (preg_match('/^[A-Za-z_][A-Za-z0-9_]*\\.\*(\s*,\s*[A-Za-z_][A-Za-z0-9_]*\\.\*)+$/', $value) === 1) { |
| 853 | return $value; |
| 854 | } |
| 855 | |
| 856 | return $this->quoteQualifiedIdentifier($value); |
| 857 | }, $this->select)); |
| 858 | } |
| 859 | |
| 860 | // Add DISTINCT or DISTINCT ON |
| 861 | $distinctClause = ''; |
| 862 | if (!empty($this->distinctOn)) { |
| 863 | // DISTINCT ON - verify dialect support |
| 864 | if (!$this->dialect->supportsDistinctOn()) { |
| 865 | throw new RuntimeException( |
| 866 | 'DISTINCT ON is not supported by ' . get_class($this->dialect) |
| 867 | ); |
| 868 | } |
| 869 | $columns = array_map( |
| 870 | fn ($col) => $this->dialect->quoteIdentifier($col), |
| 871 | $this->distinctOn |
| 872 | ); |
| 873 | $distinctClause = 'DISTINCT ON (' . implode(', ', $columns) . ') '; |
| 874 | } elseif ($this->distinct) { |
| 875 | $distinctClause = 'DISTINCT '; |
| 876 | } |
| 877 | |
| 878 | $from = $this->normalizeTable(); |
| 879 | $sql .= "SELECT {$distinctClause}{$select} FROM {$from}"; |
| 880 | |
| 881 | if (!empty($this->joinBuilder->getJoins())) { |
| 882 | $sql .= ' ' . implode(' ', $this->joinBuilder->getJoins()); |
| 883 | } |
| 884 | |
| 885 | $sql .= $this->conditionBuilder->buildConditionsClause($this->conditionBuilder->getWhere(), 'WHERE'); |
| 886 | |
| 887 | if (!empty($this->group)) { |
| 888 | $sql .= ' GROUP BY ' . $this->group; |
| 889 | } |
| 890 | |
| 891 | $sql .= $this->conditionBuilder->buildConditionsClause($this->conditionBuilder->getHaving(), 'HAVING'); |
| 892 | |
| 893 | // If there are UNION operations, add ORDER BY/LIMIT/OFFSET after UNION |
| 894 | if (empty($this->unions)) { |
| 895 | if (!empty($this->order)) { |
| 896 | $sql .= ' ORDER BY ' . implode(', ', $this->order); |
| 897 | } |
| 898 | |
| 899 | if ($this->limit !== null) { |
| 900 | $sql .= ' LIMIT ' . (int)$this->limit; |
| 901 | } |
| 902 | |
| 903 | if ($this->offset !== null) { |
| 904 | $sql .= ' OFFSET ' . (int)$this->offset; |
| 905 | } |
| 906 | |
| 907 | $sql = $this->dialect->formatSelectOptions($sql, $this->options); |
| 908 | } else { |
| 909 | // For UNION, format options first, then add UNION, then ORDER BY/LIMIT/OFFSET |
| 910 | $sql = $this->dialect->formatSelectOptions($sql, $this->options); |
| 911 | $sql = $this->buildUnionSql($sql); |
| 912 | |
| 913 | // Add ORDER BY/LIMIT/OFFSET after UNION operations |
| 914 | if (!empty($this->order)) { |
| 915 | $sql .= ' ORDER BY ' . implode(', ', $this->order); |
| 916 | } |
| 917 | |
| 918 | if ($this->limit !== null) { |
| 919 | $sql .= ' LIMIT ' . (int)$this->limit; |
| 920 | } |
| 921 | |
| 922 | if ($this->offset !== null) { |
| 923 | $sql .= ' OFFSET ' . (int)$this->offset; |
| 924 | } |
| 925 | } |
| 926 | |
| 927 | return trim($sql); |
| 928 | } |
| 929 | |
| 930 | /** |
| 931 | * Set query compilation cache. |
| 932 | * |
| 933 | * @param QueryCompilationCache|null $cache Compilation cache instance |
| 934 | * |
| 935 | * @return static |
| 936 | */ |
| 937 | public function setCompilationCache(?QueryCompilationCache $cache): static |
| 938 | { |
| 939 | $this->compilationCache = $cache; |
| 940 | return $this; |
| 941 | } |
| 942 | |
| 943 | /** |
| 944 | * Build SQL for UNION operations. |
| 945 | * |
| 946 | * @param string $baseSql Base SELECT SQL. |
| 947 | * |
| 948 | * @return string Complete SQL with UNION operations. |
| 949 | */ |
| 950 | protected function buildUnionSql(string $baseSql): string |
| 951 | { |
| 952 | $sql = $baseSql; |
| 953 | |
| 954 | foreach ($this->unions as $union) { |
| 955 | $query = $union->getQuery(); |
| 956 | $type = $union->getType(); |
| 957 | |
| 958 | if ($query instanceof \Closure) { |
| 959 | $qb = new QueryBuilder($this->connection); |
| 960 | $query($qb); |
| 961 | $unionSqlData = $qb->toSQL(); |
| 962 | $unionSql = $unionSqlData['sql']; |
| 963 | // Merge parameters from union query |
| 964 | foreach ($unionSqlData['params'] as $key => $value) { |
| 965 | $this->parameterManager->setParam($key, $value); |
| 966 | } |
| 967 | } else { |
| 968 | // QueryBuilder instance |
| 969 | $unionSqlData = $query->toSQL(); |
| 970 | $unionSql = $unionSqlData['sql']; |
| 971 | // Merge parameters from union query |
| 972 | foreach ($unionSqlData['params'] as $key => $value) { |
| 973 | $this->parameterManager->setParam($key, $value); |
| 974 | } |
| 975 | } |
| 976 | |
| 977 | $sql .= " {$type} {$unionSql}"; |
| 978 | } |
| 979 | |
| 980 | return $sql; |
| 981 | } |
| 982 | |
| 983 | /** |
| 984 | * Resolve selected key. |
| 985 | * |
| 986 | * @return ?string |
| 987 | */ |
| 988 | protected function resolveSelectedKey(): ?string |
| 989 | { |
| 990 | if (count($this->select) !== 1) { |
| 991 | return null; |
| 992 | } |
| 993 | |
| 994 | $expr = $this->select[0]; |
| 995 | |
| 996 | // 1) Try to capture explicit alias at the end: " ... AS alias" or " ... alias" |
| 997 | // Allow optional quoting with backticks, double quotes or square brackets. |
| 998 | if (preg_match('/\s+(?:AS\s+)?[`"\[]?([A-Za-z0-9_]+)[`"\]]?\s*$/i', $expr, $matches)) { |
| 999 | return $matches[1]; |
| 1000 | } |
| 1001 | |
| 1002 | // 2) If expression is a simple identifier (table.col or col), return last segment |
| 1003 | if (preg_match('/^[A-Za-z0-9_\.]+$/', $expr)) { |
| 1004 | $parts = explode('.', $expr); |
| 1005 | return end($parts); |
| 1006 | } |
| 1007 | |
| 1008 | // 3) Complex expression without alias — cannot determine key |
| 1009 | return $expr; |
| 1010 | } |
| 1011 | |
| 1012 | /** |
| 1013 | * Check if a table is referenced in the current query. |
| 1014 | * |
| 1015 | * @param string $tableName The table name to check |
| 1016 | * |
| 1017 | * @return bool True if table is in current query |
| 1018 | */ |
| 1019 | protected function isTableInCurrentQuery(string $tableName): bool |
| 1020 | { |
| 1021 | $currentTables = $this->getCurrentTables(); |
| 1022 | |
| 1023 | foreach ($currentTables as $table) { |
| 1024 | // Handle aliases (e.g., 'users AS u' -> 'u') |
| 1025 | $alias = $this->extractTableAlias($table); |
| 1026 | if ($alias === $tableName || $table === $tableName) { |
| 1027 | return true; |
| 1028 | } |
| 1029 | } |
| 1030 | |
| 1031 | return false; |
| 1032 | } |
| 1033 | |
| 1034 | /** |
| 1035 | * Get all tables referenced in the current query. |
| 1036 | * |
| 1037 | * @return array<string> Array of table names/aliases |
| 1038 | */ |
| 1039 | protected function getCurrentTables(): array |
| 1040 | { |
| 1041 | $tables = []; |
| 1042 | |
| 1043 | // Main table |
| 1044 | if ($this->table) { |
| 1045 | $tables[] = $this->table; |
| 1046 | } |
| 1047 | |
| 1048 | // JOIN tables |
| 1049 | foreach ($this->joinBuilder->getJoins() as $join) { |
| 1050 | // Extract table name from JOIN string (e.g., "LEFT JOIN users ON ..." -> "users") |
| 1051 | if (preg_match('/JOIN\s+([a-zA-Z_][a-zA-Z0-9_]*(?:\s+AS\s+[a-zA-Z_][a-zA-Z0-9_]*)?)/i', $join, $matches)) { |
| 1052 | $tables[] = trim($matches[1]); |
| 1053 | } |
| 1054 | } |
| 1055 | |
| 1056 | return $tables; |
| 1057 | } |
| 1058 | |
| 1059 | /** |
| 1060 | * Extract table alias from table reference. |
| 1061 | * |
| 1062 | * @param string $tableReference The table reference (e.g., 'users AS u', 'users') |
| 1063 | * |
| 1064 | * @return string The alias or table name |
| 1065 | */ |
| 1066 | protected function extractTableAlias(string $tableReference): string |
| 1067 | { |
| 1068 | // Handle 'table AS alias' pattern |
| 1069 | if (preg_match('/^(.+?)\s+AS\s+(.+)$/i', trim($tableReference), $matches)) { |
| 1070 | return trim($matches[2]); |
| 1071 | } |
| 1072 | |
| 1073 | return trim($tableReference); |
| 1074 | } |
| 1075 | |
| 1076 | /* ---------------- Pagination methods ---------------- */ |
| 1077 | |
| 1078 | /** |
| 1079 | * Paginate the query results with full metadata. |
| 1080 | * |
| 1081 | * Performs two queries: COUNT(*) for total and SELECT for items. |
| 1082 | * Best for traditional page-number pagination. |
| 1083 | * |
| 1084 | * @param int $perPage Items per page |
| 1085 | * @param int|null $page Current page (null = auto-detect from $_GET['page']) |
| 1086 | * @param array<string, mixed> $options Additional options (path, query) |
| 1087 | * |
| 1088 | * @return PaginationResult |
| 1089 | * @throws PDOException |
| 1090 | */ |
| 1091 | public function paginate(int $perPage = 15, ?int $page = null, array $options = []): PaginationResult |
| 1092 | { |
| 1093 | // Auto-detect page from query string if not provided |
| 1094 | if ($page === null) { |
| 1095 | $page = isset($_GET['page']) ? (int)$_GET['page'] : 1; |
| 1096 | } |
| 1097 | |
| 1098 | $page = max(1, $page); |
| 1099 | |
| 1100 | // Get CTE parameters (same for both queries) |
| 1101 | $cteParams = []; |
| 1102 | if ($this->cteManager && !$this->cteManager->isEmpty()) { |
| 1103 | $cteParams = $this->cteManager->getParams(); |
| 1104 | } |
| 1105 | |
| 1106 | // Build count SQL |
| 1107 | $countSql = $this->buildSelectSql(); |
| 1108 | $countSql = (string) preg_replace('/^SELECT\s+.*?\s+FROM/is', 'SELECT COUNT(*) as total FROM', $countSql); |
| 1109 | $countSql = (string) preg_replace('/\s+(ORDER BY|LIMIT|OFFSET)\s+.*/is', '', $countSql); |
| 1110 | |
| 1111 | // Get copy of params for count query (merge with CTE params) |
| 1112 | $countParams = array_merge($cteParams, $this->parameterManager->getParams()); |
| 1113 | |
| 1114 | // Build items SQL with pagination |
| 1115 | $savedLimit = $this->limit; |
| 1116 | $savedOffset = $this->offset; |
| 1117 | $offset = ($page - 1) * $perPage; |
| 1118 | $this->limit($perPage)->offset($offset); |
| 1119 | $itemsSql = $this->buildSelectSql(); |
| 1120 | $itemsParams = array_merge($cteParams, $this->parameterManager->getParams()); |
| 1121 | |
| 1122 | // Restore original state |
| 1123 | if ($savedLimit !== null) { |
| 1124 | $this->limit($savedLimit); |
| 1125 | } else { |
| 1126 | $this->limit = null; |
| 1127 | } |
| 1128 | if ($savedOffset !== null) { |
| 1129 | $this->offset($savedOffset); |
| 1130 | } else { |
| 1131 | $this->offset = null; |
| 1132 | } |
| 1133 | |
| 1134 | // Execute both queries |
| 1135 | $totalResult = $this->executionEngine->fetch($countSql, $countParams); |
| 1136 | $total = (int)($totalResult['total'] ?? 0); |
| 1137 | |
| 1138 | $items = $this->executionEngine->fetchAll($itemsSql, $itemsParams); |
| 1139 | |
| 1140 | return new PaginationResult($items, $total, $perPage, $page, $options); |
| 1141 | } |
| 1142 | |
| 1143 | /** |
| 1144 | * Simple pagination without total count. |
| 1145 | * |
| 1146 | * Performs only one query, making it faster than paginate(). |
| 1147 | * Best for infinite scroll or when total count is not needed. |
| 1148 | * |
| 1149 | * @param int $perPage Items per page |
| 1150 | * @param int|null $page Current page (null = auto-detect) |
| 1151 | * @param array<string, mixed> $options Additional options |
| 1152 | * |
| 1153 | * @return SimplePaginationResult |
| 1154 | * @throws PDOException |
| 1155 | */ |
| 1156 | public function simplePaginate(int $perPage = 15, ?int $page = null, array $options = []): SimplePaginationResult |
| 1157 | { |
| 1158 | if ($page === null) { |
| 1159 | $page = isset($_GET['page']) ? (int)$_GET['page'] : 1; |
| 1160 | } |
| 1161 | |
| 1162 | $page = max(1, $page); |
| 1163 | |
| 1164 | // Fetch one extra item to check if there are more pages |
| 1165 | $offset = ($page - 1) * $perPage; |
| 1166 | $items = $this->limit($perPage + 1)->offset($offset)->get(); |
| 1167 | |
| 1168 | $hasMore = count($items) > $perPage; |
| 1169 | if ($hasMore) { |
| 1170 | array_pop($items); // Remove the extra item |
| 1171 | } |
| 1172 | |
| 1173 | return new SimplePaginationResult($items, $perPage, $page, $hasMore, $options); |
| 1174 | } |
| 1175 | |
| 1176 | /** |
| 1177 | * Cursor-based pagination. |
| 1178 | * |
| 1179 | * Most efficient for large datasets and real-time data. |
| 1180 | * Requires ORDER BY clause to determine cursor columns. |
| 1181 | * |
| 1182 | * @param int $perPage Items per page |
| 1183 | * @param string|Cursor|null $cursor Current cursor (null = first page) |
| 1184 | * @param array<string, mixed> $options Additional options |
| 1185 | * |
| 1186 | * @return CursorPaginationResult |
| 1187 | * @throws PDOException |
| 1188 | */ |
| 1189 | public function cursorPaginate( |
| 1190 | int $perPage = 15, |
| 1191 | string|Cursor|null $cursor = null, |
| 1192 | array $options = [] |
| 1193 | ): CursorPaginationResult { |
| 1194 | // Decode cursor if string |
| 1195 | if (is_string($cursor)) { |
| 1196 | $cursor = Cursor::decode($cursor); |
| 1197 | } |
| 1198 | |
| 1199 | // Auto-detect cursor from query string |
| 1200 | if ($cursor === null && isset($_GET['cursor'])) { |
| 1201 | $cursor = Cursor::decode($_GET['cursor']); |
| 1202 | } |
| 1203 | |
| 1204 | // Determine cursor columns from ORDER BY |
| 1205 | $cursorColumns = $this->getCursorColumns(); |
| 1206 | if (empty($cursorColumns)) { |
| 1207 | throw new RuntimeException('Cursor pagination requires ORDER BY clause'); |
| 1208 | } |
| 1209 | |
| 1210 | // Apply cursor conditions if provided |
| 1211 | if ($cursor !== null) { |
| 1212 | $this->applyCursorConditions($cursor, $cursorColumns); |
| 1213 | } |
| 1214 | |
| 1215 | // Fetch items |
| 1216 | $items = $this->limit($perPage + 1)->get(); |
| 1217 | $hasMore = count($items) > $perPage; |
| 1218 | |
| 1219 | if ($hasMore) { |
| 1220 | array_pop($items); // Remove extra item |
| 1221 | } |
| 1222 | |
| 1223 | // Create cursors |
| 1224 | $previousCursor = null; |
| 1225 | $nextCursor = $hasMore && count($items) > 0 |
| 1226 | ? Cursor::fromItem($items[count($items) - 1], $cursorColumns) |
| 1227 | : null; |
| 1228 | |
| 1229 | return new CursorPaginationResult($items, $perPage, $previousCursor, $nextCursor, $options); |
| 1230 | } |
| 1231 | |
| 1232 | /** |
| 1233 | * Get cursor columns from ORDER BY clause. |
| 1234 | * |
| 1235 | * @return array<int, string> |
| 1236 | */ |
| 1237 | protected function getCursorColumns(): array |
| 1238 | { |
| 1239 | $columns = []; |
| 1240 | foreach ($this->order as $orderExpr) { |
| 1241 | // Extract column name from "column ASC" or "column DESC" |
| 1242 | if (preg_match('/^([^\s]+)/', $orderExpr, $matches)) { |
| 1243 | $columns[] = trim($matches[1], '"`'); |
| 1244 | } |
| 1245 | } |
| 1246 | return $columns; |
| 1247 | } |
| 1248 | |
| 1249 | /** |
| 1250 | * Apply cursor conditions to query. |
| 1251 | * |
| 1252 | * @param Cursor $cursor |
| 1253 | * @param array<int, string> $columns |
| 1254 | */ |
| 1255 | protected function applyCursorConditions(Cursor $cursor, array $columns): void |
| 1256 | { |
| 1257 | $params = $cursor->parameters(); |
| 1258 | |
| 1259 | // For simplicity, build individual column comparisons |
| 1260 | // More advanced: composite key comparison for better performance |
| 1261 | foreach ($columns as $col) { |
| 1262 | if (isset($params[$col])) { |
| 1263 | $paramName = 'cursor_' . $col; |
| 1264 | $this->conditionBuilder->where($col, $params[$col], '>'); |
| 1265 | } |
| 1266 | } |
| 1267 | } |
| 1268 | |
| 1269 | /* ---------------- Cache helpers ---------------- */ |
| 1270 | |
| 1271 | /** |
| 1272 | * Check if caching should be used for this query. |
| 1273 | */ |
| 1274 | protected function shouldUseCache(): bool |
| 1275 | { |
| 1276 | return $this->cacheEnabled && $this->cacheManager !== null; |
| 1277 | } |
| 1278 | |
| 1279 | /** |
| 1280 | * Get cached result if available. |
| 1281 | * |
| 1282 | * @return mixed|null Cached result or null if not found |
| 1283 | */ |
| 1284 | protected function getFromCache(): mixed |
| 1285 | { |
| 1286 | if ($this->cacheManager === null) { |
| 1287 | return null; |
| 1288 | } |
| 1289 | |
| 1290 | // If custom cache key provided, use it directly (no SQL compilation needed) |
| 1291 | if ($this->cacheKey !== null) { |
| 1292 | $this->cachedCacheKey = $this->cacheKey; |
| 1293 | $cached = $this->cacheManager->get($this->cacheKey); |
| 1294 | // If cache hit, we don't need to compile SQL |
| 1295 | if ($cached !== null) { |
| 1296 | return $cached; |
| 1297 | } |
| 1298 | // Cache miss, but we'll need SQL later anyway, so continue |
| 1299 | } |
| 1300 | |
| 1301 | // For auto-generated keys, we need SQL to generate the key |
| 1302 | // Generate SQL once and cache it for potential reuse |
| 1303 | if ($this->cachedSqlData === null) { |
| 1304 | $this->cachedSqlData = $this->toSQL(); |
| 1305 | } |
| 1306 | |
| 1307 | // Generate cache key once and reuse it |
| 1308 | if ($this->cachedCacheKey === null) { |
| 1309 | $this->cachedCacheKey = $this->generateCacheKeyFromSqlData($this->cachedSqlData); |
| 1310 | } |
| 1311 | |
| 1312 | return $this->cacheManager->get($this->cachedCacheKey); |
| 1313 | } |
| 1314 | |
| 1315 | /** |
| 1316 | * Save result to cache. |
| 1317 | * |
| 1318 | * @param mixed $result The result to cache |
| 1319 | */ |
| 1320 | protected function saveToCache(mixed $result): void |
| 1321 | { |
| 1322 | if ($this->cacheManager === null) { |
| 1323 | return; |
| 1324 | } |
| 1325 | |
| 1326 | // Use cached cache key if available (generated in getFromCache) |
| 1327 | if ($this->cachedCacheKey === null) { |
| 1328 | // If custom key provided, use it |
| 1329 | if ($this->cacheKey !== null) { |
| 1330 | $this->cachedCacheKey = $this->cacheKey; |
| 1331 | } else { |
| 1332 | // Generate key from SQL |
| 1333 | $sqlData = $this->cachedSqlData ?? $this->toSQL(); |
| 1334 | $this->cachedCacheKey = $this->generateCacheKeyFromSqlData($sqlData); |
| 1335 | } |
| 1336 | } |
| 1337 | |
| 1338 | $ttl = $this->cacheTtl ?? $this->cacheManager->getConfig()->getDefaultTtl(); |
| 1339 | $this->cacheManager->set($this->cachedCacheKey, $result, $ttl); |
| 1340 | } |
| 1341 | |
| 1342 | /** |
| 1343 | * Generate cache key for current query. |
| 1344 | */ |
| 1345 | protected function generateCacheKey(): string |
| 1346 | { |
| 1347 | if ($this->cacheKey !== null) { |
| 1348 | return $this->cacheKey; |
| 1349 | } |
| 1350 | |
| 1351 | if ($this->cacheManager === null) { |
| 1352 | return ''; |
| 1353 | } |
| 1354 | |
| 1355 | // Use cached SQL data if available |
| 1356 | $sqlData = $this->cachedSqlData ?? $this->toSQL(); |
| 1357 | return $this->generateCacheKeyFromSqlData($sqlData); |
| 1358 | } |
| 1359 | |
| 1360 | /** |
| 1361 | * Generate cache key from SQL data. |
| 1362 | * |
| 1363 | * @param array{sql: string, params: array<string, mixed>} $sqlData |
| 1364 | * |
| 1365 | * @return string |
| 1366 | */ |
| 1367 | protected function generateCacheKeyFromSqlData(array $sqlData): string |
| 1368 | { |
| 1369 | if ($this->cacheManager === null) { |
| 1370 | return ''; |
| 1371 | } |
| 1372 | |
| 1373 | $driver = $this->connection->getDialect()->getDriverName(); |
| 1374 | return $this->cacheManager->generateKey($sqlData['sql'], $sqlData['params'], $driver); |
| 1375 | } |
| 1376 | } |